package com.warm.common.excel;


import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @Date: 2019/11/12 17:26
 * @Author: LChuang
 * @Description:
 */
public class ExcelUtil {

    private String sheetName;

    private String[] rowNames;

    private Tree rowTree;

    private Tree colTree;

    private List<Object[]> dataList = new ArrayList<>();

    private Workbook wb;

    private Sheet sheet;

    private TreeTool treeTool;

    private Map<String, CellStyle> styles;

    private Integer rowStart;

    private Integer colStart;

    public ExcelUtil(String sheetName, String[] rowNames) {
        this.sheetName = sheetName;
        this.rowNames = rowNames;
    }

    public ExcelUtil(String sheetName, Tree rowTree) {
        this.sheetName = sheetName;
        this.rowTree = rowTree;
    }

    public ExcelUtil(String sheetName, Tree rowTree, Tree colTree) {
        this.sheetName = sheetName;
        this.rowTree = rowTree;
        this.colTree = colTree;
    }

    private void init(){
        this.wb = new XSSFWorkbook();
        this.styles = createStyles(wb);
        this.sheet = wb.createSheet(sheetName);
    }

    public void export(List<Object[]> dataList){
        this.dataList = dataList;
        this.export();
    }

    public void export(){
        OutputStream out = null;
        try {
            init();
            this.createTitle();
            if(!dataList.isEmpty()){
                setData();
            }
            String filename = encodingFilename(sheetName);
            out = new FileOutputStream(getAbsoluteFile(filename));
            wb.write(out);
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("导出Excel失败，请联系网站管理员！");
        } finally {
            closeStream(wb, out);
        }
    }

    public void export(List<Object[]> dataList, HttpServletResponse response){
        this.dataList = dataList;
        this.export(response);
    }

    public void export(HttpServletResponse response){
        OutputStream out = null;
        try {
            init();
            this.createTitle();
            if(!dataList.isEmpty()){
                setData();
            }
            String filename = encodingFilename(sheetName);
            this.setResponseHeader(response, filename);
            out = new FileOutputStream(getAbsoluteFile(filename));
            wb.write(out);
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("导出Excel失败，请联系网站管理员！");
        } finally {
            closeStream(wb, out);
        }
    }

    private void createTitle(){
        if(this.rowNames != null){
            // single title --> create one row
            singleTitle();
        }else {
            this.treeTool = new TreeTool();
            if(this.rowTree != null && this.colTree != null){
                this.multTreeTitle();
            }else {
                this.singleTreeTitle();
            }
        }
    }

    private void setData(){
        for(int i = 0; i < dataList.size(); i++){
            Object[] objs = dataList.get(i);
            for(int j = 0; j < objs.length; j++){
                Row row = sheet.getRow(rowStart+i);
                if(row == null){
                    row = sheet.createRow(rowStart+i);
                }
                Cell cell = row.createCell(colStart+j, Cell.CELL_TYPE_STRING);
                if(objs[j] != null && !"".equals(objs[j])){
                    cell.setCellValue(objs[j].toString());
                }else {
                    cell.setCellValue("");
                }
                cell.setCellStyle(styles.get("data"));
            }
        }
    }

    private void multTreeTitle(){
        String[][] table = treeTool.combineTable(rowTree, colTree);
        this.rowStart = rowTree.getLevel()+1;
        this.colStart = colTree.getLevel()+1;
        //create cells
        genCells(table);
        //merge cells
        mergeCells(table);
    }

    private void singleTreeTitle(){
        String[][] table = treeTool.treeToTable(rowTree);
        this.rowStart = rowTree.getLevel()+1;
        this.colStart = 0;
        //create cells
        genCells(table);
        //merge cells
        mergeCells(table);
    }

    private void singleTitle(){
        this.rowStart = 1;
        this.colStart = 0;
        Row row = sheet.createRow(0);
        for(int i=0; i < rowNames.length; i++){
            // create cell
            Cell cell = row.createCell(i);
            // set cell
            cell.setCellValue(rowNames[i]);
            cell.setCellStyle(styles.get("header"));
            sheet.autoSizeColumn(i, true);
        }
    }

    private void genCells(String[][] table){
        Row row = null;
        // combine Table
        for(int i = 0; i < table.length; i++){
            row = sheet.createRow(i);
            for(int j = 0; j < table[0].length; j++){
                Cell cell = row.createCell(j);
                cell.setCellValue(table[i][j]);
                if(table[i][j] == null || "".equals(table[i][j])){
                    cell.setCellStyle(styles.get("data"));
                }else {
                    cell.setCellStyle(styles.get("header"));
                }
                sheet.autoSizeColumn(i, true);
            }
        }
    }

    private void mergeCells(String[][] table){
        // table --> list<String>
        List<String> strs = new ArrayList<>();

        for(int i = 0; i < table.length; i++){
            for(int j = 0; j < table[0].length; j++){
                if(table[i][j] != null && !"".equals(table[i][j])){
                    strs.add(table[i][j]);
                }
            }
        }

        // get duplicate element
        List<String> dupls = getDupls(strs);

        // mergeCell
        dupls.forEach(str -> {
            mergeCell(table, str);
        });
    }

    private void mergeCell(String[][] table, String item){
        List<Integer> x = new ArrayList<>();
        List<Integer> y = new ArrayList<>();
        for(int i = 0; i < table.length; i++){
            for(int j = 0; j < table[0].length; j++){
                if(item.equals(table[i][j])){
                    x.add(i);
                    y.add(j);
                }
            }
        }
        int minX = Collections.min(x);
        int maxX = Collections.max(x);
        int minY = Collections.min(y);
        int maxY = Collections.max(y);
        sheet.addMergedRegion(new CellRangeAddress(minX, maxX, minY, maxY));
    }

    private List<String> getDupls(List<String> list){
        List<String> dupls = list.stream()
                .filter(item -> item != null && !"".equals(item))
                .collect(Collectors.toMap(e -> e, e -> 1, Integer::sum))
                .entrySet().stream()
                .filter(entry -> entry.getValue() > 1)
                .map(entry -> entry.getKey())
                .collect(Collectors.toList());
        return dupls;
    }

    private Map<String, CellStyle> createStyles(Workbook wb) {
        // 写入各条记录,每条记录对应excel表中的一行
        Map<String, CellStyle> styles = new HashMap<String, CellStyle>();
        CellStyle style = wb.createCellStyle();
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        Font dataFont = wb.createFont();
        dataFont.setFontName("Arial");
        dataFont.setFontHeightInPoints((short) 10);
        style.setFont(dataFont);
        styles.put("data", style);

        style = wb.createCellStyle();
        style.cloneStyleFrom(styles.get("data"));
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setFillForegroundColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        Font headerFont = wb.createFont();
        headerFont.setFontName("Arial");
        headerFont.setFontHeightInPoints((short) 10);
        headerFont.setBold(true);
        headerFont.setColor(IndexedColors.WHITE.getIndex());
        style.setFont(headerFont);
        styles.put("header", style);

        return styles;
    }

    public String encodingFilename(String filename) {
        filename = filename + "_" + UUID.randomUUID().toString() + ".xlsx";
        return filename;
    }

    private void setResponseHeader(HttpServletResponse response, String fileName) {
        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
            // 前端需使用decodeURI(fileName)解码
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            response.setCharacterEncoding("UTF-8");
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
            response.addHeader("Access-Control-Expose-Headers", "Content-Disposition");
            response.setStatus(201);
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    private void closeStream(Workbook wb, OutputStream out){
        if (wb != null) {
            try {
                wb.close();
            } catch (IOException e1) {
                e1.printStackTrace();
            }
        }
        if (out != null) {
            try {
                out.close();
            } catch (IOException e1) {
                e1.printStackTrace();
            }
        }
    }

    public String getAbsoluteFile(String filename) {
        String downloadPath = "D://" + filename;
        File desc = new File(downloadPath);
        if (!desc.getParentFile().exists()) {
            desc.getParentFile().mkdirs();
        }
        return downloadPath;
    }
}
